<?php if (!defined("_ECRIRE_INC_VERSION")) return; if (class_exists('ProductExcel')) return;
/**
 * EcataloguE
 *
 * @author     Aleksey Yaroshchuk (aleksey.yaroshchuk@gmail.com)
 * @license    GNU/GPL
 * @package    plugins
 * @subpackage ecatalogue
 * @copyright  (c) Aleksey Yaroshchuk 2011-2012
 * @link       http://www.spip-contrib.net/EcataloguE
 */



/**
 *  This class maintain Excel download/upload operations with products 
 *
 *  @author Aleksey Yaroshchuk (aleksey.yaroshchuk@gmail.com) 
 */






class ProductExcel {   
    private $product_mdl;
    private $article_mdl;
    private $rubrique_mdl;    
    private $mot_lien_mdl;    

    
    /**
     *  Boolean value wich tells us wether status update is needed when updating from Excel 
     */
    public $UpdateStatus = false;
    
    /**
     *  Array of rubriques, articles from wich must be downloaded in Excel file
     */
    public $DownloadRubriques = array();
    
    /**
     *  ID of Mot, articles with wich will be downloaded
     */
    public $DownloadIdMot = false;
    
    public function __construct() {
        include_spip('functions/pdk_common');
        include_spip('formulaires/selecteur/generique_fonctions');
        
        $this->product_mdl = pdk_load_class('ProductModel','classes/');
        $this->article_mdl = pdk_load_class('PdkArticleModel','classes/models');
        $this->rubrique_mdl = pdk_load_class('PdkRubriqueModel','classes/models');
        $this->rubrique_mdl2 = pdk_load_class('RubriqueModel','classes');
        $this->mot_lien_mdl = pdk_load_class('PdkMotLienModel','classes/models');
        
        require_once _DIR_PLUGIN_EXCEL."pear/Spreadsheet/Excel/Writer.php";
        
        $this->UpdateStatus = lire_config('ecatalogue/excel_update_status');
        $this->DownloadRubriques = picker_selected(lire_config('ecatalogue/branches'), 'rubrique');
    }
    
    public function OutputExcel() {
        // Creating a workbook
        $workbook = new Spreadsheet_Excel_Writer();    
        $workbook->setTempDir(_DIR_CACHE);
        $workbook->setVersion(8);
        
        // sending HTTP headers
        $workbook->send('products.xls');
    
        // Let's create catalogue worksheet:
        $this->addWorksheet($workbook);
        
        // Let's send the file
        $workbook->close();
    
        // After file has been sent, just exit:
        exit;
    }
    
    /**
     *  Gets tmp directory full system path
     */
    public function GetTmpDir() {
        $dir = str_replace(DIRECTORY_SEPARATOR.'ecrire','',getcwd());        
        $dir = $dir.DIRECTORY_SEPARATOR.'tmp/'; 
        return $dir; 
    }
    
    /**
     *  @param string $filename Filename (needed for unit testing)
     */
    public function BackupExcel($filename = '') {        
        // Creating a workbook
        if  ($filename)
            //$workbook = new Spreadsheet_Excel_Writer(_DIR_TMP.$filename);
            $workbook = new Spreadsheet_Excel_Writer($this->GetTmpDir().$filename);
        else
            //$workbook = new Spreadsheet_Excel_Writer(_DIR_TMP.'products_'.date("d-m-Y_H-i-s").'.xls');    
            $workbook = new Spreadsheet_Excel_Writer($this->GetTmpDir().'products_'.date("d-m-Y_H-i-s").'.xls');
        
        $workbook->setVersion(8);
    
        // Let's create catalogue worksheet:
        $this->addWorksheet($workbook);
        
        // Let's send the file
        $workbook->close();
    }
    
    /**
     *  Adds Worksheet with clients to given $workbook
     */
    private function addWorksheet(&$workbook) {
        // Creating worksheet    
        $worksheet =& $workbook->addWorksheet('Products');
        $worksheet->setInputEncoding('utf-8');
        
        // Format for first line
        $format_bold =& $workbook->addFormat();
        $format_bold->setBold();
        
        $worksheet->write(0, 0, _T('ecatalogue:id_article'), $format_bold);
        $worksheet->write(0, 1, _T('ecatalogue:sku'), $format_bold);
        $worksheet->write(0, 2, _T('ecatalogue:product_id_rubrique'), $format_bold);
        $worksheet->write(0, 3, _T('ecatalogue:product_rubrique'), $format_bold);
        $worksheet->write(0, 4, _T('ecatalogue:product_name'), $format_bold);
        $worksheet->write(0, 5, _T('ecatalogue:quantity'), $format_bold);
        $worksheet->write(0, 6, _T('ecatalogue:price'), $format_bold);
        $worksheet->write(0, 7, _T('ecatalogue:priceold'), $format_bold);
        
        
        // Set columns width: 
        $worksheet->setColumn(0,5, 15);
        
        $articles = $this->article_mdl->select("statut!='poubelle'",'','id_article');
        
        /*
        $download_rubriques = picker_selected(_request('download_rubriques'), 'rubrique');
        if (!$download_rubriques)
            //$download_rubriques = picker_selected(lire_config('ecatalogue/branches'), 'rubrique');     
            $download_rubriques = $this->DownloadRubriques;
        */
                
        $row_num = 1;
        foreach ($articles as $article) {
            
            $download_rubrique = false;
            if (isset($this->DownloadRubriques) && is_array($this->DownloadRubriques))
                foreach ($this->DownloadRubriques as $id_rubrique) {
                    if ($this->rubrique_mdl->CheckRubriqueInRubrique($article['id_rubrique'], $id_rubrique) || $article['id_rubrique'] == $id_rubrique)
                        $download_rubrique = true;
                }
            if (!isset($this->DownloadRubriques) || !is_array($this->DownloadRubriques) || empty($this->DownloadRubriques) ||
                $download_rubrique) {                    
                    // If we have to download only articles with specified id_mot, check wether  
                    if ($this->DownloadIdMot && !$this->mot_lien_mdl->select(array('id_mot='.$this->DownloadIdMot, 'id_objet='.$article['id_article'], "objet='article'")))
                        continue;
                $product = $this->product_mdl->select_by_id($article['id_article'], true);
                $rubrique = $this->rubrique_mdl->select_by_id($article['id_rubrique']);    
                $worksheet->write($row_num, 0, $article['id_article']);
                $worksheet->write($row_num, 1, $product['sku']);
                $worksheet->write($row_num, 2, $article['id_rubrique']);
                $worksheet->write($row_num, 3, $rubrique['titre']);
                $worksheet->write($row_num, 4, $article['titre']);
                $worksheet->write($row_num, 5, $product['quantity']);            
                $worksheet->write($row_num, 6, $product['price']);
                $worksheet->write($row_num, 7, $product['priceold']);
                $row_num++;
            } 
        }        
    } 
    
    /**
     *  Upload Excel file and update products from it
     *  @param string $primary_key Primary key - used to find article to update. 
     *  @param bool If is set to true, new article will be created in case article isn't found and id_rubrique column has value
     */
    public function UpdateExcel($primary_key='id_article', $create_new=false,$id_reserv=0) {        
        $this->BackupExcel();
        $file_path = $this->uploadExcel();
        $this->updateProductsFromExcel($file_path, $primary_key, $create_new,$id_reserv);
        unlink($file_path); // Delete Excel file
    }
	
	/**
     *  Upload Excel file and update price products from it
     *  @param string $primary_key Primary key - used to find article to update. 
     */
    public function UpdatePriceExcel($primary_key='id_article') {        
        
        $file_path = $this->uploadExcel();
        $this->updatePriceFromExcel($file_path, $primary_key);
        unlink($file_path); // Delete Excel file
    }
	
	
	
    
    /**
     *  Upload Excel file 
     *  @return string File path
     */
    private function uploadExcel() {
        include_spip('classes/pdk_upload');
        $config = array();
        $config['upload_path'] = _DIR_TMP;
        $config['allowed_types'] = 'csv|xls|xlsx'; 
        $config['max_size'] = '10048';
        $config['encrypt_name'] = TRUE;
        $uploader = new pdk_Upload($config);
        
        if (!$uploader->do_upload('excel_products')) 
            throw new Exception(_T('ecatalogue:excel_file_upload_error')); 
            
        $file_data = $uploader->data();
        return $file_data['full_path'];
    }
    
    
   private function update_price_by_group($id_article,$id_group,$price)
   {
       $res = sql_select('*','spip_ecatalogue_prices',array('id_group = '.$id_group,'id_article = '.$id_article)); 
       if ($res)
       {
          while($row = sql_fetch($res))
          {
             sql_update('spip_ecatalogue_prices',array('price'=>$price),array('id_group = '.$id_group,'id_article = '.$id_article));
          }
     
       }
       else
       {
          sql_insert('spip_ecatalogue_prices','(id_article,id_group,price)','('.$id_article.','.$id_group.','.id_price.')');  
       }
       
   
   } 
	
	
	public function updatePriceFromExcel($file_path, $primary_key='id_article') 
  {
        require_once _DIR_PLUGIN_EXCEL."pear/Spreadsheet/Excel/Reader.php";
        
        ini_set('max_execution_time', 0);
        
        
        $col_primary = 1;
        $path_info = pathinfo($file_path);
        $type_file =$path_info['extension'];
        
        if ($type_file == 'csv')
        {
              //echo '<h1>THIS CSV FILE!!!</h1>';
              
              $fd = fopen($file_path,  "r"); 
              $i=0;
              while (!feof($fd))  
              {
                $fields_win1251 = fgets($fd);
                $fields_UTF8 = iconv("windows-1251", "UTF-8", $fields_win1251);
                $fields = explode(";",$fields_UTF8);
                $product_key = $fields[1];
                $product_quantity = $fields[3];
                $group_index = 4;
                
                $res = sql_select('*','spip_ecatalogue_prices_group','is_active = 1');
                $groups = array();
                while ($group = sql_fetch($res)) 
                {
	                 $groups[] = array('id_group'=>$group['id_group'],'titre'=>$group['titre']); 
                }
                $k = 0;
                
                foreach ($groups as $key=>$value) 
                {
                    $price = isset($fields[$group_index+$k])?$fields[$group_index+$k]:'';
                    if ($price)
                    {
                        $id_group = $value['id_group'];
                        if ($primary_key == 'id_article')
                        {
                            $product_exist = $this->product_mdl->select_by_id(intval($primary_val));
                            if ($product_exist)
                             $this->update_price_by_group($product_key,$id_group,$price);
                            $id_article =  $product_key;
                        
                        } 
                        else
                        {
                            
                             $product_exist = $this->product_mdl->select($primary_key." = '".mysql_real_escape_string($product_key)."'");
                             
                             if ($product_exist)
                             {
                                $id_article = isset($product_exist[0]['id_article'])? $product_exist[0]['id_article']:'';
                             }
                             if ($id_article)
                             $this->update_price_by_group($id_article,$id_group,$price);
                        }     
                             
                    }
                       // Обновляем количество 
                    $product_data['quantity'] = $product_quantity;
                    if (!$this->product_mdl->update_by_id($id_article, $product_data)) 
                    {                            
                            throw new Exception("Error while updating product #".$id_article);
                    }   
                    $k++;	
                }
                
            $i++;
		        
        
           }
           spip_log("$i", 'excel.'._LOG_ERREUR);                
           fclose($fd) ;
           
        }
//Excel
        else
        {
              $reader = new Spreadsheet_Excel_Reader();
              $reader->setUTFEncoder('iconv');        
              $reader->setOutputEncoding('UTF-8');
              $reader->read($file_path);
        
        
            foreach($reader->sheets as $k=>$data) 
            {
              // $reader->boundsheets[$k]['name']; // Sheet name            
              foreach($data["cells"] as $row_num=>$row) 
              {
                
                //ppre($row);
                
                $product_key = $row[2];
                $product_quantity = $row[4];
                $group_index = 5;
                
                $res = sql_select('*','spip_ecatalogue_prices_group','is_active = 1');
                $groups = array();
                while ($group = sql_fetch($res)) 
                {
	                 $groups[] = array('id_group'=>$group['id_group'],'titre'=>$group['titre']); 
                }
                $k = 0;
                
                foreach ($groups as $key=>$value) 
                {
                    $price = isset($row[$group_index+$k])?$row[$group_index+$k]:'';
                    if ($price)
                    {
                        $id_group = $value['id_group'];
                        if ($primary_key == 'id_article')
                        {
                            $product_exist = $this->product_mdl->select_by_id(intval($primary_val));
                            if ($product_exist)
                             $this->update_price_by_group($product_key,$id_group,$price);
                            $id_article =  $product_key;
                        
                        } 
                        else
                        {
                             //ppr($primary_key." = '".mysql_real_escape_string($product_key)."'");
                             $product_exist = $this->product_mdl->select($primary_key." = '".mysql_real_escape_string($product_key)."'");
                             //ppr($product_exist);
                             
                             
                             if ($product_exist)
                             {
                                $id_article = isset($product_exist[0]['id_article'])? $product_exist[0]['id_article']:'';
                             }
                             if ($id_article)
                             $this->update_price_by_group($id_article,$id_group,$price);
                        }     
                             
                    }
                       // Обновляем количество 
                    $product_data['quantity'] = $product_quantity;
                    
                    if ($id_article)
                    if (!$this->product_mdl->update_by_id($id_article, $product_data)) 
                    {                            
                            throw new Exception("Error while updating product #".$id_article);
                    }   
                    $k++;	
                }
                
            $i++;
		        
        
                
                
              }
            }  
            
        }
        
        
    }
    
    /**
     *  Update products from given Excel file
     *  @param string $file_path Excel file path
     */
    public function updateProductsFromExcel($file_path, $primary_key='id_article', $create_new=false,$id_reserv=0) {
        require_once _DIR_PLUGIN_EXCEL."pear/Spreadsheet/Excel/Reader.php";
        
        ini_set('max_execution_time', 0);
        
        
        switch ($primary_key) {
            case 'id_article': $col_primary = 1; break;
            case 'sku': $col_primary = 2; break;
            default: $col_primary = 1; break;
        }
        
        
        
        $path_info = pathinfo($file_path);
        $type_file =$path_info['extension'];
        
        if ($type_file == 'csv')
        {
              //echo '<h1>THIS CSV FILE!!!</h1>';
              
              $fd = fopen($file_path,  "r") ; 
              $i=0;
               while (!feof($fd))  
              {
                $fields_win1251 = fgets($fd);
                $fields_UTF8 = iconv("windows-1251", "UTF-8", $fields_win1251);
                $fields = explode(";",$fields_UTF8);
                
                
                
                $product_article = ($fields[0]!=0)?$fields[0]:$field[4];
                $product_name = $fields[1];
                $product_manufacture = $fields[2];
                $product_price = $fields[3];
                $product_key = $fields[4];
                $product_id_rubrique = $fields[5];
                $product_id_extended_rubrique = $fields[6];
                
                $id_article = false;
                $primary_val = $row["$col_primary"]; // First cell has index 1!                
                //if ($primary_val) {
                    $product_data = array();
                    
                    $product_data['sku'] = $product_article;                       
                    $product_data['titre'] = $product_name;
                    $product_data['extra1'] = $product_manufacture;
                    $product_data['extra2'] = $product_key;
                    
                    
                    $product_data['price'] = $product_price;
                   
                    
                    $article_data = array();
                    $article_data['titre'] = $product_name;
                    
                    // Update article status?                    
                    if ($this->UpdateStatus) {
                        $quantity = intval($product_data['quantity']);                        
                        if ($quantity > 0)
                            $article_data['statut'] = 'publie';
                        else
                            $article_data['statut'] = 'prepa';                             
                    }          
                    
                    // To avoid ambiguity select by id if $primary_key = 'id_article'
                    if ($primary_key == 'id_article' && $primary_val) {
                        $product_exist = $this->product_mdl->select_by_id(intval($primary_val));
                    } else if ($primary_val) {
                        $product_exist = $this->product_mdl->select($primary_key." = '".mysql_real_escape_string($primary_val)."'");
                        if ($product_exist) $product_exist = $product_exist[0];
                    } else {
                        $product_exist = false;
                    }
                    
                   
                     
                    
                    if ((!isset($product_exist) || !$product_exist) && $create_new) { // Create new product?
                    
                        $id_rubrique = intval($product_id_extended_rubrique );
                        
                        if (!$id_rubrique)
                        {           
                            $id_rubrique = intval($product_id_rubrique) ;
                            
                            if (!$id_rubrique) $id_rubrique = $id_reserv; // to do make reserv
                        }
                        
                                                                        
                        $rubrique = $this->rubrique_mdl->select_by_id($id_rubrique);
                        
                        if(!$rubrique)
                        {
                            $rubrique = $this->rubrique_mdl2->get_rubric_by_id_import($id_rubrique);
                            $id_rubrique = $rubrique['id_rubrique'];
                        }
                        
                        //spip_log($rubrique, 'excel.'._LOG_ERREUR);
                        //echo '<pre>';
                        //print_r($rubrique);
                        //echo '</pre>';
                        //exit;
                        
                        if (!$rubrique) continue;
                        $article_data['id_rubrique'] = $id_rubrique;
                        $article_data['id_secteur'] = $rubrique['id_secteur'];                        
                        $article_data['statut'] = 'prepa'; // For new article statut must be defined
                        $article_data['lang'] = $rubrique['lang'];
                        
                        // Insert article
                        if (!$id_article = $this->article_mdl->insert($article_data))
                            throw new Exception("Error while inserting article ".$primary_val);
                        
                        // Insert product
                        $product_data['id_article'] = $id_article;                                                
                        if (!$this->product_mdl->insert($product_data))
                            throw new Exception("Error while inserting product #".$id_article);
                            
                    } 
                    
                    
                    else if ($product_exist) 
                    
                    { // Update old product
                        $id_article = $product_exist['id_article'];
                        
                        $id_rubrique = intval($product_id_extended_rubrique );
                        
                        if (!$id_rubrique)
                        {           
                            $id_rubrique = intval($product_id_rubrique);
                            
                            if (!$id_rubrique) $id_rubrique = $id_reserv; // to do make reserv
                        }
                        
                                                                        
                        $rubrique = $this->rubrique_mdl->select_by_id($id_rubrique);
                        
                        if(!$rubrique)
                        {
                            $rubrique = $this->rubrique_mdl2->get_rubric_by_id_import($id_rubrique);
                            $id_rubrique = $rubrique['id_rubrique'];
                        }
                        
                        
                        if (!$rubrique) continue;
                        $article_data['id_rubrique'] = $id_rubrique;
                        $article_data['id_secteur'] = $rubrique['id_secteur']; 
                        
                        // Update product                                                
                        if (!$this->product_mdl->update_by_id($id_article, $product_data)) {                            
                            throw new Exception("Error while updating product #".$id_article);
                        }   
                        
                        // Update article
                        if (!$this->article_mdl->update_by_id($id_article, $article_data))
                            throw new Exception("Error while updating article #".$id_article);                        
                    }
                
                
                
                $i++;
		        spip_log("$i", 'excel.'._LOG_ERREUR);                
                
              }
fclose($fd) ;
              
              
              
              
        
        
        }

//Excel
        else
        {
              $reader = new Spreadsheet_Excel_Reader();
        $reader->setUTFEncoder('iconv');        
        $reader->setOutputEncoding('UTF-8');
        $reader->read($file_path);
        
        spip_log("EXCEL IMPORT START", 'excel.'._LOG_ERREUR);
        
        
        
        
        
        foreach($reader->sheets as $k=>$data) {
            // $reader->boundsheets[$k]['name']; // Sheet name            
            foreach($data["cells"] as $row_num=>$row) {
                
                if (($row_num == 1)||($row_num == 2))
                {
                    spip_log($row_num, 'excel.'._LOG_ERREUR);
                    spip_log('row_0'.$row[0], 'excel.'._LOG_ERREUR);            
                    spip_log('row_1'.$row[1], 'excel.'._LOG_ERREUR);            
                    spip_log('row_2'.$row[2], 'excel.'._LOG_ERREUR);            
                    spip_log('row_3'.$row[3], 'excel.'._LOG_ERREUR);            
                    spip_log('row_4'.$row[4], 'excel.'._LOG_ERREUR);
                    spip_log('row_5'.$row[5], 'excel.'._LOG_ERREUR); 
                    spip_log('row_6'.$row[6], 'excel.'._LOG_ERREUR); 
                    spip_log('row_7'.$row[7], 'excel.'._LOG_ERREUR); 
                
                }
                
                
                if ($row_num == 1) continue; //omit first row 
                $id_article = false;
                $primary_val = $row["$col_primary"]; // First cell has index 1!                
                //if ($primary_val) {
                    $product_data = array();
                    $product_data['sku'] = $row["2"];
                    //$product_data['titre'] = $row["4"];
                    $product_data['quantity'] = $row["6"];
                    $product_data['price'] = $row["7"];
                    $product_data['priceold'] = $row["8"];  
                    
                    $article_data = array();
                    $article_data['titre'] = $row["5"];
                    
                    // Update article status?                    
                    if ($this->UpdateStatus) {
                        $quantity = intval($product_data['quantity']);                        
                        if ($quantity > 0)
                            $article_data['statut'] = 'publie';
                        else
                            $article_data['statut'] = 'prepa';                             
                    }          
                    
                    // To avoid ambiguity select by id if $primary_key = 'id_article'
                    if ($primary_key == 'id_article' && $primary_val) {
                        $product_exist = $this->product_mdl->select_by_id(intval($primary_val));
                    } else if ($primary_val) {
                        $product_exist = $this->product_mdl->select($primary_key." = '".mysql_real_escape_string($primary_val)."'");
                        if ($product_exist) $product_exist = $product_exist[0];
                    } else {
                        $product_exist = false;
                    }
                    
                   
                     
                    
                    if ((!isset($product_exist) || !$product_exist) && $create_new) { // Create new product?
                    
                        $id_rubrique = intval($row['3']);
                        
                        if (!$id_rubrique) $id_rubrique = intval($row['4']);
                        
                        spip_log('ID_RUBRIQUE:'.$id_rubrique, 'excel.'._LOG_ERREUR); 
                        
                        if (!$id_rubrique) continue;                                                
                        $rubrique = $this->rubrique_mdl->select_by_id($id_rubrique);
                        
                        if(!$rubrique)
                        {
                            $rubrique = $this->rubrique_mdl2->get_rubric_by_id_import($id_rubrique);
                            $id_rubrique = $rubrique['id_rubrique'];
                        }
                        
                        //spip_log($rubrique, 'excel.'._LOG_ERREUR);
                        //echo '<pre>';
                        //print_r($rubrique);
                        //echo '</pre>';
                        //exit;
                        
                        if (!$rubrique) continue;
                        $article_data['id_rubrique'] = $id_rubrique;
                        $article_data['id_secteur'] = $rubrique['id_secteur'];                        
                        $article_data['statut'] = 'prepa'; // For new article statut must be defined
                        $article_data['lang'] = $rubrique['lang'];
                        
                        // Insert article
                        if (!$id_article = $this->article_mdl->insert($article_data))
                            throw new Exception("Error while inserting article ".$primary_val);
                        
                        // Insert product
                        $product_data['id_article'] = $id_article;                                                
                        if (!$this->product_mdl->insert($product_data))
                            throw new Exception("Error while inserting product #".$id_article);
                            
                    } else if ($product_exist) { // Update old product
                        $id_article = $product_exist['id_article'];
                        
                        // Update product                                                
                        if (!$this->product_mdl->update_by_id($id_article, $product_data)) {                            
                            throw new Exception("Error while updating product #".$id_article);
                        }   
                        
                        // Update article
                        if (!$this->article_mdl->update_by_id($id_article, $article_data))
                            throw new Exception("Error while updating article #".$id_article);                        
                    }
                //}               
            }
        } // end foreach woorkbook
        
        
        
        }
        
        
    } 
} 
?>